import java.sql.*;
import java.util.Scanner;

public class Main {

    public static void main(String[] args) {
        boolean uo = true;
        while (uo) {
            System.out.println("请输入你想进行功能1.全查询（遍历）2.模糊查询3.添加4.删除5.修改6.退出");
            Scanner scanner = new Scanner(System.in);
            boolean hu = true;
            while (hu) {
                String gh = scanner.nextLine();
                try {
                    int yj = Integer.parseInt(gh.trim());
                    if (yj >= 1 && yj <= 5) {
                        switch (yj) {
                            case 1:
                                m1();
                                break;
                            case 2:
                                m2();
                                break;
                            case 3:
                                m3();
                                break;
                            case 4:
                                m4();
                                break;
                            case 5:
                                m5();
                                break;
                        }
                        hu = false;
                    } else if (yj==6) {hu=false;uo=false;

                    } else {
                        System.out.println("你输入的数字有误请重新输入");
                    }
                } catch (NumberFormatException e) {
                    System.out.println(e+"你输入的数字格式有误请重新输入");
                }
            }


        }
    }
    public static void m1(){//全查询
        String url = "jdbc:mysql://localhost:3306/jdbc";
        String user = "root";
        String pw = "123456";
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
           Connection connection = DriverManager.getConnection(url,user,pw);
            if (connection!=null){
                System.out.println("连接成功");
            }
            String sql="select * from user";
            PreparedStatement de=connection.prepareStatement(sql);
           ResultSet ui= de.executeQuery();
            while (ui.next()){
                System.out.println("id:"+ui.getInt("id")+",name:"+ui.getString("name")+",password:"+ui.getString("pass"));
            }
            connection.close();
            de.close();
            ui.close();
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
      catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }
    public static void m2(){//模糊查询
        String url = "jdbc:mysql://localhost:3306/jdbc";
        String user = "root";
        String pw = "123456";
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection connection = DriverManager.getConnection(url,user,pw);
            if (connection!=null){
                System.out.println("连接成功");
            }
            System.out.println("请输入你想查询的姓名关键词");
            Scanner scanner=new Scanner(System.in);
            String na= scanner.nextLine();
            String sql="select * from user where name like ?";
            PreparedStatement de=connection.prepareStatement(sql);
         de.setString(1,"%"+na+"%");
            ResultSet ui= de.executeQuery();
            while (ui.next()){
                System.out.println("id:"+ui.getInt("id")+",name:"+ui.getString("name")+",password:"+ui.getString("pass"));
            }
            connection.close();
            de.close();
            ui.close();
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
        catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }
    public static void m3(){//添加
        String url = "jdbc:mysql://localhost:3306/jdbc";
        String user = "root";
        String pw = "123456";
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection connection = DriverManager.getConnection(url,user,pw);
            if (connection!=null){
                System.out.println("连接成功");
            }
            System.out.println("请输入你的姓名和密码");
            Scanner scanner=new Scanner(System.in);
            String name=scanner.next();
            String passward=scanner.next();
            String sql="insert into user(name,pass) values (?,?)";
            PreparedStatement de=connection.prepareStatement(sql);
            de.setString(1,name);
            de.setString(2,passward);
          de.executeUpdate();
            System.out.println("已成功添加您的账户");
            connection.close();
            de.close();
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
        catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    public static void m4(){//删除
        String url = "jdbc:mysql://localhost:3306/jdbc";
        String user = "root";
        String pw = "123456";
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection connection = DriverManager.getConnection(url,user,pw);
            if (connection!=null){
                System.out.println("连接成功");
            }
            System.out.println("请输入您想删除的用户名");
            Scanner scanner=new Scanner(System.in);
            String name=scanner.next();
            String sql="delete from user where name=?";
            PreparedStatement de=connection.prepareStatement(sql);
            de.setString(1,name);
            de.executeUpdate();
            System.out.println("已成功删除");
            connection.close();
            de.close();
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
        catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    public static void m5(){//修改

        String url = "jdbc:mysql://localhost:3306/jdbc";
        String user = "root";
        String pw = "123456";
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection connection = DriverManager.getConnection(url,user,pw);
            if (connection!=null){
                System.out.println("连接成功");
            }
            Scanner scanner=new Scanner(System.in);
            System.out.println("请输入您想修改的用户的用户名");
            String lname=scanner.nextLine();
            System.out.println("请输入新的用户名和密码");
            String xname=scanner.next();
            String mima=scanner.next();
            String sql="select id from user where name=?";
            PreparedStatement de=connection.prepareStatement(sql);
            de.setString(1,lname);
           ResultSet ft=de.executeQuery();
          int h= chaid(connection,lname);
           String xsql="update user set name=?,pass=? where id=?";
           PreparedStatement gh=connection.prepareStatement(xsql);
           gh.setString(1,xname);
           gh.setString(2,mima);
           gh.setInt(3,h);
           gh.executeUpdate();
            System.out.println("修改成功");
           connection.close();
            de.close();
            ft.close();
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
        catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    public static int chaid(Connection con,String name){
        String dr="Select id from user where name=?";
        try {PreparedStatement po=con.prepareStatement(dr);
            po.setString(1,name);
            ResultSet rt=po.executeQuery();
            if(rt.next()){
                return rt.getInt("id");
            }

        }catch (SQLException e){
            System.out.println("查找用户id出现错误"+e);
        }
        return -1;
    }

}